﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    /// <summary>
    /// wp code
    /// </summary>
    public partial class PIPE_INFO
    {
        public static int pageNum = 20;
        /// <summary>
        /// Query all pipes under strip
        /// </summary>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="taskTotalPages"></param>
        /// <returns></returns>
        public static DataTable QueryPIPE_INFOByStripNo(string stripNo, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sqlPipe = string.Format("select * from (select a.*,ROWNUM rn from {0} a where {1}='{2}' order by {3} asc,{4} asc) where rn>{5} and rn<={6}",
                  tableName, fd_stripNo, stripNo, fd_updateTime, fd_pipeNo, minRow, maxRow);
                string sqlPipeCount = string.Format("select count(*) from (select * from {0} where {1}='{2}')",
                   tableName, fd_stripNo, stripNo);
                DataTable pipeTable = OracleHelper.Exct_QueryTable(sqlPipe);
                if (pipeTable != null)
                {
                    int pipePages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlPipeCount));
                    if (pipePages % pageNum == 0)
                    {
                        TotalPages = (pipePages / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (pipePages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("QueryPIPE_INFOByStripNo Success");
                    return pipeTable;
                }
                else
                {
                    TotalPages = "";
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryPIPE_INFOByStripNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Query all Pipes by page
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="taskTotalPages"></param>
        /// <returns></returns>
        public static DataTable Query_PIPE_PARAMETER(string sql, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sqlPipe = string.Format("select * from ({0} order by {1} asc,{2} asc) where rn>{3} and rn<={4}",
                  sql, fd_updateTime, fd_pipeNo, minRow, maxRow);
                string sqlPipeCount = string.Format("select count(*) from ({0})",
                   sql);
                DataTable pipeTable = OracleHelper.Exct_QueryTable(sqlPipe);
                if (pipeTable != null)
                {
                    int pipePages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlPipeCount));
                    if (pipePages % pageNum == 0)
                    {
                        TotalPages = (pipePages / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (pipePages / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("Query_PIPE_PARAMETER Success");
                    return pipeTable;
                }
                else
                {
                    TotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("Query_PIPE_PARAMETER", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Query task, strip, pipe information through batchNo
        /// </summary>
        /// <param name="batchNo"></param>
        /// <param name="stripTotalPages"></param>
        /// <param name="pipeTotalPages"></param>
        /// <returns></returns>
        public static DataTable[] QueryBatchByBatchNo(string batchNo, out string stripTotalPages, out string pipeTotalPages, out Dictionary<string, double> dic)
        {
            try
            {
                string sqlTask = string.Format("select * from {0} b where b.{1} in (select a.{2} from {3} a where {4}='{5}' or {6}='{7}')",
                  TASK_INFO.tableName, TASK_INFO.fd_taskNo, STRIP_INFO.fd_taskNo, STRIP_INFO.tableName, STRIP_INFO.fd_batchNoE, batchNo, STRIP_INFO.fd_batchNoO, batchNo);

                string sqlStrip = string.Format("select * from (select ROWNUM rn,a.* from (select * from {0} where {1}='{2}' or {3}='{4}' order by {5} desc) a) where rn>0 and rn<=20",
                  STRIP_INFO.tableName, STRIP_INFO.fd_batchNoE, batchNo, STRIP_INFO.fd_batchNoO, batchNo, STRIP_INFO.fd_updateTime);
                string sqlStripCount = string.Format("select count(*) from (select * from {0} where {1}='{2}' or {3}='{4}' order by {5} desc)",
                  STRIP_INFO.tableName, STRIP_INFO.fd_batchNoE, batchNo, STRIP_INFO.fd_batchNoO, batchNo, STRIP_INFO.fd_updateTime);

                string sqlPipe = string.Format("select * from (select rownum rn,b.* from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3}='{4}'  group by a.{5}) order by {6} desc) b) where rn>0 and rn <=20",
                  tableName, fd_updateTime, tableName, fd_batchNo, batchNo, fd_pipeNo, fd_updateTime);
                string sqlPipeCount = string.Format("select count(*) from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3}='{4}' group by a.{5}) order by {6} desc)",
                  tableName, fd_updateTime, tableName, fd_batchNo, batchNo, fd_pipeNo, fd_updateTime);

                DataTable taskTable = OracleHelper.Exct_QueryTable(sqlTask);
                DataTable stripTable = OracleHelper.Exct_QueryTable(sqlStrip);
                DataTable pipeTable = OracleHelper.Exct_QueryTable(sqlPipe);
                DataTable[] tables;

                //钢管密度
                double density = 7850;//单位：kg/m3
                double stripWd = Convert.ToDouble(stripTable.Rows[0][STRIP_INFO.fd_stripWidth].ToString()) * 0.0001;//单位：m
                double stripTHK = Convert.ToDouble(stripTable.Rows[0][STRIP_INFO.fd_stripThk].ToString()) * 0.00001;//单位：m
                double pipeTotalNums = 0;
                double pipeTotalLength = 0;
                double pipeTotalWeight = 0;
                double pipeGoodTotalNums = 0;
                double pipeGoodTotalLength = 0;
                double pipeGoodTotalWeight = 0;
                double pipeScrapTotalNums = 0;
                double pipeScrapTotalLength = 0;
                double pipeScrapTotalWeight = 0;

                string pipeTotalNumsSql = string.Format("select COUNT(*) from (select * from PIPE_INFO  where PIPE_NO in (select a.PIPE_NO from PIPE_INFO a where a.BATCH_NO='{0}'  group by a.PIPE_NO) and location='FCO' order by PIPE_NO desc)", batchNo);
                pipeTotalNums = getNumber(pipeTotalNumsSql);
                string pipeTotalLengthSql = string.Format("select sum(length) from (select * from PIPE_INFO  where PIPE_NO in (select a.PIPE_NO from PIPE_INFO a where a.BATCH_NO='{0}'  group by a.PIPE_NO) and location='FCO' order by PIPE_NO desc)", batchNo);
                pipeTotalLength = Math.Round(getNumber(pipeTotalLengthSql) * 0.001, 3);//单位：m
                pipeTotalWeight = Math.Round(pipeTotalLength * stripWd * stripTHK * density, 3);//单位：kg

                string pipeScrapBC1NumsSql = string.Format("select COUNT(*) from (select * from PIPE_INFO  where PIPE_NO in (select a.PIPE_NO from PIPE_INFO a where a.BATCH_NO='{0}'  group by a.PIPE_NO) and location='BC1' order by PIPE_NO desc)", batchNo);
                double pipeScrapBC1Nums = getNumber(pipeScrapBC1NumsSql);
                string pipeScrapBC1LengthSql = string.Format("select sum(length) from (select * from PIPE_INFO  where PIPE_NO in (select a.PIPE_NO from PIPE_INFO a where a.BATCH_NO='{0}'  group by a.PIPE_NO) and location='BC1' order by PIPE_NO desc)", batchNo);
                double pipeScrapBC1Length = getNumber(pipeScrapBC1LengthSql) * 0.001;//单位：m

                string pipeScrapPLSNumsSql = string.Format("select COUNT(*) from (select * from PIPE_INFO  where PIPE_NO in (select a.PIPE_NO from PIPE_INFO a where a.BATCH_NO='{0}'  group by a.PIPE_NO) and location='PLS' order by PIPE_NO desc)", batchNo);
                double pipeScrapPLSNums = getNumber(pipeScrapPLSNumsSql);
                string pipeScrapPLSLengthSql = string.Format("select sum(length) from (select * from PIPE_INFO  where PIPE_NO in (select a.PIPE_NO from PIPE_INFO a where a.BATCH_NO='{0}'  group by a.PIPE_NO) and location='PLS' order by PIPE_NO desc)", batchNo);
                double pipeScrapPLSLength = getNumber(pipeScrapPLSLengthSql) * 0.001;//单位：m

                pipeScrapTotalNums = Math.Round(pipeScrapBC1Nums, 3);
                pipeScrapTotalLength = Math.Round(pipeScrapBC1Length + pipeTotalLength - pipeScrapPLSLength, 3);
                pipeScrapTotalWeight = Math.Round(pipeScrapTotalLength * stripWd * stripTHK * density, 3);//单位：kg

                pipeGoodTotalNums = Math.Round(pipeTotalNums - pipeScrapTotalNums, 3);
                pipeGoodTotalLength = Math.Round(pipeTotalLength - pipeScrapTotalLength, 3);
                pipeGoodTotalWeight = Math.Round(pipeGoodTotalLength * stripWd * stripTHK * density, 3);//单位：kg

                dic = new Dictionary<string, double>();
                dic.Clear();
                dic.Add("pipeTotalNums", pipeTotalNums);
                dic.Add("pipeTotalLength", pipeTotalLength);
                dic.Add("pipeTotalWeight", pipeTotalWeight);
                dic.Add("pipeGoodTotalNums", pipeGoodTotalNums);
                dic.Add("pipeGoodTotalLength", pipeGoodTotalLength);
                dic.Add("pipeGoodTotalWeight", pipeGoodTotalWeight);
                dic.Add("pipeScrapTotalNums", pipeScrapTotalNums);
                dic.Add("pipeScrapTotalLength", pipeScrapTotalLength);
                dic.Add("pipeScrapTotalWeight", pipeScrapTotalWeight);

                if (taskTable != null && stripTable != null && pipeTable != null)
                {
                    tables = new DataTable[] { taskTable, stripTable, pipeTable };

                    int stripCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlStripCount));
                    if (stripCounts % pageNum == 0)
                    {
                        stripTotalPages = (stripCounts / pageNum).ToString();
                    }
                    else
                    {
                        stripTotalPages = (stripCounts / pageNum + 1).ToString();
                    }
                    int pipeCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlPipeCount));
                    if (pipeCounts % pageNum == 0)
                    {
                        pipeTotalPages = (pipeCounts / pageNum).ToString();
                    }
                    else
                    {
                        pipeTotalPages = (pipeCounts / pageNum + 1).ToString();
                    }


                    LogHelper.WriteLog("QueryPIPE_INFOByStripNo Success");
                    return tables;
                }
                else
                {
                    stripTotalPages = "";
                    pipeTotalPages = "";
                    dic = null;
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryBatchByBatchNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Query strip information through batchNo pagination
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="TotalPages"></param>
        /// <returns></returns>
        public static DataTable QueryBatchStripByBatchNo(string batchNo, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sql = string.Format("select * from (select ROWNUM rn,a.* from (select * from {0} where {1}='{2}' or {3}='{4}' order by {5} desc) a) where rn>{6} and rn<={7}",
                  STRIP_INFO.tableName, STRIP_INFO.fd_batchNoE, batchNo, STRIP_INFO.fd_batchNoO, batchNo, STRIP_INFO.fd_updateTime, minRow, maxRow);
                string sqlCount = string.Format("select count(*) from (select * from {0} where {1}='{2}' or {3}='{4}' order by {5} desc)",
                  STRIP_INFO.tableName, STRIP_INFO.fd_batchNoE, batchNo, STRIP_INFO.fd_batchNoO, batchNo, STRIP_INFO.fd_updateTime);
                DataTable table = OracleHelper.Exct_QueryTable(sql);
                if (table != null)
                {
                    int totalCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (totalCounts % pageNum == 0)
                    {
                        TotalPages = (totalCounts / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (totalCounts / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("QueryBatchStripByBatchNo Success");
                    return table;
                }
                else
                {
                    TotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryBatchStripByBatchNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Query Pipe information through batchNo pagination
        /// </summary>
        /// <param name="batchNo"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="TotalPages"></param>
        /// <returns></returns>
        public static DataTable QueryBatchPipeByBatchNo(string batchNo, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sql = string.Format("select * from (select rownum rn,b.* from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3}='{4}'  group by a.{5}) order by {6} desc) b) where rn>{7} and rn <={8}",
                  tableName, fd_updateTime, tableName, fd_batchNo, batchNo, fd_pipeNo, fd_updateTime, minRow, maxRow);
                string sqlCount = string.Format("select count(*) from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3}='{4}' group by a.{5}) order by {6} desc)",
                  tableName, fd_updateTime, tableName, fd_batchNo, batchNo, fd_pipeNo, fd_updateTime);
                DataTable table = OracleHelper.Exct_QueryTable(sql);
                if (table != null)
                {
                    int totalCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (totalCounts % pageNum == 0)
                    {
                        TotalPages = (totalCounts / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (totalCounts / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("QueryBatchPipeByBatchNo Success");
                    return table;
                }
                else
                {
                    TotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryBatchPipeByBatchNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Query Pipe information via stripNo pagination
        /// </summary>
        /// <param name="stripNo"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="TotalPages"></param>
        /// <returns></returns>
        public static DataTable QueryStripPipeByStripNo(string stripNo, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sql = string.Format("select * from (select rownum rn,b.* from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3}='{4}'  group by a.{5}) order by {6} desc) b) where rn>{7} and rn <={8}",
                  tableName, fd_updateTime, tableName, fd_stripNo, stripNo, fd_pipeNo, fd_updateTime, minRow, maxRow);
                string sqlCount = string.Format("select count(*) from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3}='{4}' group by a.{5}) order by {6} desc)",
                  tableName, fd_updateTime, tableName, fd_stripNo, stripNo, fd_pipeNo, fd_updateTime);
                DataTable table = OracleHelper.Exct_QueryTable(sql);
                if (table != null)
                {
                    int totalCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (totalCounts % pageNum == 0)
                    {
                        TotalPages = (totalCounts / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (totalCounts / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("QueryStripPipeByStripNo Success");
                    return table;
                }
                else
                {
                    TotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryStripPipeByStripNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Fuzzy query of Pipe information through PipeNo pagination
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <param name="minRow"></param>
        /// <param name="maxRow"></param>
        /// <param name="TotalPages"></param>
        /// <returns></returns>
        public static DataTable QueryPipePipeByPipeNo(string pipeNo, int minRow, int maxRow, out string TotalPages)
        {
            try
            {
                string sql = string.Format("select * from (select rownum rn,b.* from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3} like '%{4}%' and update_time<>'0'  group by a.{5}) order by {6} desc) b) where rn>{7} and rn <={8}",
                  tableName, fd_updateTime, tableName, fd_pipeNo, pipeNo, fd_pipeNo, fd_updateTime, minRow, maxRow);
                string sqlCount = string.Format("select count(*) from (select * from {0}  where {1} in (select MAX(update_time) as update_time from {2} a where a.{3} like '%{4}%'  and a.update_time<>'0' group by a.{5}) order by {6} desc)",
                  tableName, fd_updateTime, tableName, fd_pipeNo, pipeNo, fd_pipeNo, fd_updateTime);
                DataTable table = OracleHelper.Exct_QueryTable(sql);
                if (table != null)
                {
                    int totalCounts = Convert.ToInt32(TASK_INFO.GetTableCount(sqlCount));
                    if (totalCounts % pageNum == 0)
                    {
                        TotalPages = (totalCounts / pageNum).ToString();
                    }
                    else
                    {
                        TotalPages = (totalCounts / pageNum + 1).ToString();
                    }
                    LogHelper.WriteLog("QueryPipePipeByPipeNo Success");
                    return table;
                }
                else
                {
                    TotalPages = "";
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryPipePipeByPipeNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Query Pipepassport and task information through PipeNo
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <param name="dic"></param>
        /// <returns></returns>
        public static DataTable QueryPipePipePassportAndTaskByPipeNo(string pipeNo, out Dictionary<string, string> dic)
        {
            try
            {
                string sql = string.Format("select * from {0} where {1} in (select a.{2} from {3} a where a.{4}='{5}')",
                  TASK_INFO.tableName, TASK_INFO.fd_taskNo, fd_taskNo, tableName, fd_pipeNo, pipeNo);

                DataTable table = OracleHelper.Exct_QueryTable(sql);
                dic = new Dictionary<string, string>();
                if (table != null)
                {
                    //sql = string.Format("select c.PIPE_OD,c.STRIP_WIDTH,c.STRIP_THICKNESS,d.PIPE_DURABILITY_CLASS,d.PIPE_STEEL_GRADE,e.is_ex,e.is_spy,g.pipe_no,g.length,g.update_time from RECIPE_INFO c,(select * from TASK_INFO b where TASK_NO in (select a.TASK_NO from PIPE_INFO a where a.pipe_no='{0}')) d,(select * from STRIP_INFO f where f.STRIP_NO in (select a.STRIP_NO from PIPE_INFO a where a.pipe_no='{1}')) e,(select * from PIPE_INFO a where a.pipe_no='{2}') g where c.RECIPE_NO in (select b.RECIPE_NO from TASK_INFO b where TASK_NO in (select a.TASK_NO from PIPE_INFO a where a.pipe_no='{3}'))",
                    //	pipeNo,pipeNo,pipeNo,pipeNo);
                    string recipeSql = string.Format("select * from recipe_info where RECIPE_NO in (select b.RECIPE_NO from TASK_INFO b where TASK_NO in (select a.TASK_NO from PIPE_INFO a where a.pipe_no='{0}'))", pipeNo);
                    string taskSql = string.Format("select * from TASK_INFO b where TASK_NO in (select a.TASK_NO from PIPE_INFO a where a.pipe_no='{0}')", pipeNo);
                    string stripSql = string.Format("select * from STRIP_INFO f where f.STRIP_NO in (select a.STRIP_NO from PIPE_INFO a where a.pipe_no='{0}')", pipeNo);
                    string pipeSql = string.Format("select * from PIPE_INFO a where a.pipe_no='{0}'", pipeNo);
                    DataTable recipeTable = OracleHelper.Exct_QueryTable(recipeSql);
                    DataTable taskTable = OracleHelper.Exct_QueryTable(taskSql);
                    DataTable stripTable = OracleHelper.Exct_QueryTable(stripSql);
                    DataTable pipeTable = OracleHelper.Exct_QueryTable(pipeSql);

                    //DataTable pipePassportTable = OracleHelper.Exct_QueryTable(sql);
                    if (recipeTable.Rows.Count > 0 && taskTable.Rows.Count > 0 && stripTable.Rows.Count > 0 && pipeTable.Rows.Count > 0)
                    {
                        string pipeOD = recipeTable.Rows[0][RECIPE_INFO.fd_pipeOd].ToString();
                        string stripWidth = recipeTable.Rows[0][RECIPE_INFO.fd_stripWidth].ToString();
                        string stripThickness = recipeTable.Rows[0][RECIPE_INFO.fd_stripThickness].ToString();
                        string productionStandard = taskTable.Rows[0][TASK_INFO.fd_productionStandard].ToString();
                        string pipeDurabilityClass = taskTable.Rows[0][TASK_INFO.fd_pipeDurabilityClass].ToString();
                        string pipeSteelGrade = taskTable.Rows[0][TASK_INFO.fd_pipeSteelGrade].ToString();
                        string stripNo = stripTable.Rows[0][STRIP_INFO.fd_stripNo].ToString();
                        string isEX = stripTable.Rows[0][STRIP_INFO.fd_isEx].ToString();
                        string isSPY = stripTable.Rows[0][STRIP_INFO.fd_isSpy].ToString();

                        double pipeLenght = Convert.ToDouble(pipeTable.Rows[0][fd_length].ToString());
                        DateTime dateTime = Convert.ToDateTime(pipeTable.Rows[0][fd_updateTime].ToString());
                        string date = dateTime.ToString(Formats.GetShortFormat());
                        string time = dateTime.ToString(Formats.GetTimeFormat());

                        //计算pipe的质量
                        //double density = 7850;//单位：kg/m3
                        //double stripWd = Convert.ToDouble(stripWidth) * 0.001;//单位：m
                        //double stripTHK = Convert.ToDouble(stripThickness) * 0.001;//单位：m
                        //double pipeWeight =Math.Round(density * stripWd * stripTHK * pipeLenght,3);

                        string sequenPipesql = string.Format("select rn from (select rownum rn,a.* from (select * from PIPE_INFO where update_time in (select max(update_time) from (select * from PIPE_INFO where STRIP_NO in (select STRIP_NO from PIPE_INFO where pipe_no='{0}')) group by pipe_no) order by UPDATE_TIME asc) a) where pipe_no='{1}'", pipeNo, pipeNo);
                        string sequenNumInStrip = getNumber(sequenPipesql).ToString();

                        string shiftID = TEAM_TABLE.GetCurrentShift(dateTime);


                        dic.Add(fd_pipeNo, pipeNo);
                        dic.Add("DATE", date);
                        dic.Add("TIME", time);
                        dic.Add("PIPEODWT", pipeOD + "x" + stripThickness);
                        dic.Add(TASK_INFO.fd_productionStandard, productionStandard);
                        dic.Add(TASK_INFO.fd_pipeDurabilityClass, pipeDurabilityClass);
                        dic.Add(TASK_INFO.fd_pipeSteelGrade, pipeSteelGrade);
                        dic.Add(STRIP_INFO.fd_stripNo, stripNo);
                        dic.Add("SEQUENNUMINSTRIP", sequenNumInStrip);
                        dic.Add(STRIP_INFO.fd_isEx, isEX);
                        dic.Add(STRIP_INFO.fd_isSpy, isSPY);
                        dic.Add("SHIFTID", shiftID);


                    }
                    else
                    {
                        dic = null;
                    }

                    LogHelper.WriteLog("QueryPipePipePassportAndTaskByPipeNo Success");
                    return table;
                }
                else
                {
                    return null; ;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryPipePipeByPipeNo", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Query the PipeProcessData information to be exported through PipeNo
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <returns></returns>
        public static DataTable[] QueryPipeExportPipeProcessData(string pipeNo)
        {
            try
            {
                string sql = string.Format("select * from {0} where {1}='{2}' and {3}='{4}'",
                  tableName, fd_pipeNo, pipeNo, fd_location, "FCO");
                DataTable pipeTable = OracleHelper.Exct_QueryTable(sql);
                string stripNo = pipeTable.Rows[0][fd_stripNo].ToString();
                double length = Convert.ToDouble(pipeTable.Rows[0][fd_length].ToString());
                double dtj = Convert.ToDouble(pipeTable.Rows[0][fd_dtj].ToString());
                string startDTJ = (dtj - length).ToString();
                string endDTJ = dtj.ToString();

                System.Diagnostics.Stopwatch st = new System.Diagnostics.Stopwatch();
                st.Start();
                string pipeHFWSql = string.Format("select STRIP_NO,DTJ,UPDATE_TIME,POWER,FREQ,TEMP,CURT,VOLT from {0} where {1}='{2}' and {3}>{4} and {5}<{6}", STRIP_HFW.tableName, STRIP_HFW.fd_stripNo, stripNo, STRIP_HFW.fd_dtj, startDTJ, STRIP_HFW.fd_dtj, endDTJ);
                DataTable tableHFW = OracleHelper.Exct_QueryTable(pipeHFWSql);
                string pipeLht1Sql = string.Format("select STRIP_NO,DTJ,UPDATE_TIME,POWER,FREQ,TEMP,CURT,VOLT from {0} where {1}='{2}' and {3}>{4} and {5}<{6}", STRIP_LHT1.tableName, STRIP_LHT1.fd_stripNo, stripNo, STRIP_LHT1.fd_dtj, startDTJ, STRIP_LHT1.fd_dtj, endDTJ);
                DataTable tableLht1 = OracleHelper.Exct_QueryTable(pipeLht1Sql);
                string pipeLht2Sql = string.Format("select STRIP_NO,DTJ,UPDATE_TIME,POWER,FREQ,TEMP,CURT,VOLT from {0} where {1}='{2}' and {3}>{4} and {5}<{6}", STRIP_LHT2.tableName, STRIP_LHT2.fd_stripNo, stripNo, STRIP_LHT2.fd_dtj, startDTJ, STRIP_LHT2.fd_dtj, endDTJ);
                DataTable tableLht2 = OracleHelper.Exct_QueryTable(pipeLht2Sql);
                string pipeLht3Sql = string.Format("select STRIP_NO,DTJ,UPDATE_TIME,POWER,FREQ,TEMP,CURT,VOLT from {0} where {1}='{2}' and {3}>{4} and {5}<{6}", STRIP_LHT3.tableName, STRIP_LHT3.fd_stripNo, stripNo, STRIP_LHT3.fd_dtj, startDTJ, STRIP_LHT3.fd_dtj, endDTJ);
                DataTable tableLht3 = OracleHelper.Exct_QueryTable(pipeLht3Sql);
                string pipeLht4Sql = string.Format("select STRIP_NO,DTJ,UPDATE_TIME,POWER,FREQ,TEMP,CURT,VOLT from {0} where {1}='{2}' and {3}>{4} and {5}<{6}", STRIP_LHT4.tableName, STRIP_LHT4.fd_stripNo, stripNo, STRIP_LHT4.fd_dtj, startDTJ, STRIP_LHT4.fd_dtj, endDTJ);
                DataTable tableLht4 = OracleHelper.Exct_QueryTable(pipeLht4Sql);
                string pipeSQSql = string.Format("select STRIP_NO,DTJ,UPDATE_TIME,SQ1,SQ2,SQ3,SQ4 from {0} where {1}='{2}' and {3}>{4} and {5}<{6}", STRIP_SQ.tableName, STRIP_SQ.fd_stripNo, stripNo, STRIP_SQ.fd_dtj, startDTJ, STRIP_SQ.fd_dtj, endDTJ);
                DataTable tableSQ = OracleHelper.Exct_QueryTable(pipeSQSql);
                string pipeSUSTSql = string.Format("select STRIP_NO,DTJ,UPDATE_TIME,DEFECT_TYPE from {0} where {1}='{2}' and {3}>{4} and {5}<{6}", STRIP_SUST.tableName, STRIP_SUST.fd_stripNo, stripNo, STRIP_SUST.fd_dtj, startDTJ, STRIP_SUST.fd_dtj, endDTJ);
                DataTable tableSUST = OracleHelper.Exct_QueryTable(pipeSUSTSql);
                st.Stop();
                long ts = st.ElapsedMilliseconds;
                if (tableHFW != null && tableLht1 != null && tableLht2 != null && tableLht3 != null && tableLht4 != null && tableSQ != null && tableSUST != null)
                {
                    DataTable[] tables = new DataTable[] { tableHFW, tableLht1, tableLht2, tableLht3, tableLht4, tableSQ, tableSUST };

                    LogHelper.WriteLog("QueryPipeExportPipeProcessData Success");
                    return tables;
                }
                else
                {
                    return null; ;
                }

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("QueryPipeExportPipeProcessData", ex);
                throw ex;
            }
        }
        /// <summary>
        /// get a certain value
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static double getNumber(string sql)
        {
            try
            {
                var tempNum = OracleHelper.GetSingle(sql);
                if (tempNum != null && tempNum.ToString() != "")
                {
                    return Convert.ToDouble(tempNum);
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("getNumber", ex);
                throw (ex);
            }
        }
    }
}
